﻿using FreeSqlDemo.Modules;
using FreeSqlDemo.Utility;
using Microsoft.AspNetCore.Http;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace FreeSqlDemo.Services
{
    public class NpoiExcelOperationService
    {
        public bool ExcelDataBatchImport(IFormFile formFile, out string resultMsg)
        {
            var connectionString = "Data Source = 39.105.20.156; Port = 3306; User ID = root; Password = root; Initial Catalog = demo;Charset=utf8;";
            var Fsql = new FreeSql.FreeSqlBuilder()
                       .UseConnectionString(FreeSql.DataType.MySql, connectionString)
                        .UseAutoSyncStructure(true)
                        .UseLazyLoading(true)
                        .UseNoneCommandParameter(true)
                        .Build();

            SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = "Server=39.105.20.156;Database=demo;Uid=root;Pwd=root;AllowLoadLocalInfile=true",
                DbType = DbType.MySql,
                IsAutoCloseConnection = true
            });


            resultMsg = "导入成功";


            var result = false;

            try
            {
                if (formFile.Length > 0)
                {
                    var stopWatch = new Stopwatch();
                    stopWatch.Start();//间隔运行时间开始记录
                    var getDataTable = NpoiExcelImportHelper._.ExcelToDataTable(formFile.OpenReadStream(), Path.GetExtension(formFile.FileName), out result, out resultMsg);
                    if (getDataTable.Rows.Count <= 0)
                    {
                        return false;
                    }
                    var userInfoList = new List<UserInfo>();
                    //将dataTable数据源转化为List数据源
                    for (int i = 0; i < getDataTable.Rows.Count; i++)
                    {
                        var userInfo = new UserInfo
                        {
                            UserName = getDataTable.Rows[i][0].ToString(),
                            Sex = getDataTable.Rows[i][1].ToString(),
                            Phone = getDataTable.Rows[i][2].ToString(),
                            Description = getDataTable.Rows[i][3].ToString(),
                            Hobby = getDataTable.Rows[i][4].ToString()
                        };
                        userInfoList.Add(userInfo);
                    }
                    var t2 = Fsql.Insert(userInfoList).ExecuteAffrows();//FreeSql批量插入

                    var t3 = db.Insertable(userInfoList).ExecuteCommand();  //SqlSugar批量插入

                    stopWatch.Stop();
                    resultMsg = $"恭喜您Excel导入成功,耗费总时长{stopWatch.Elapsed.TotalSeconds}秒，总共导入{getDataTable.Rows.Count}条数据";
                }
            }
            catch (Exception ex)
            {
                result = false;
                resultMsg = ex.Message;
            }
            return result;
        }

    }
}
